package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;
import jdk.nashorn.internal.scripts.JD;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements UserDao {
    /**
     * 1.sql语句
     * 2.根据查询出来的结果返回响应的数据
     *
     * @param userModel
     * @return
     */
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select * from user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
//        因为所有增删查改操作都会连接数据库，所以，将连接数据库提取到工具类里面JDBC
        UserModel model = null;
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;

    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
//        至少要知道第几页和每一页显示多少条
        String sql = "select address,id,username,phone,sex,hobby,date_format(birthday,'%Y-%m-%d') as birthday,enable,email  from user " +
                " order by id desc " +
                " limit " + (userModel.getPage() - 1) * userModel.getLimit() + "," + userModel.getLimit();
        List<UserModel> list = new ArrayList<>();
//        for (int i = 0; i < ; i++) {
//
//        }
//        因为查询出来不知道由多少条数据，所以，这里不适合用for循环，应该用while循环
//        while (true) {
//            if (1 > 2) {
//                return null;//跳出方法
////                break;//跳出循环
////                continue;//继续执行下一次循环
//                System.out.println(11);
//            }
//            System.out.println(22);
//        }
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
//                resultSet.getString("username")引号里面的对应数据库的列名
                model.setUsername(resultSet.getString("username"));
                model.setId(resultSet.getInt("id"));
                model.setPhone(resultSet.getString("phone"));
                model.setSex(resultSet.getString("sex"));
                model.setEnable(resultSet.getInt("enable"));
                model.setAddress(resultSet.getString("address"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setHobby(resultSet.getString("hobby"));
                model.setEmail(resultSet.getString("email"));
                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return list;
    }

    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(*) from user ";
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                return resultSet.getInt("count(*)");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updateSex(UserModel userModel) {
        String sql = " update user set sex= " +
                " case  sex" +
                "    when '男' then '女' " +
                "    when '女' then '男' " +
                " end " +
                " where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int updateEnable(UserModel userModel) {
//        enable=1-enable这个必须满足条件 enable只能为1和0两个数字
//        如果enable改为其他数字则sql语句
//        String s = "update user set enable="+userModel.getEnable()+" where id="+userModel.getId();
        String sql = " update user set enable=1-enable " +

                " where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int add(UserModel userModel) {
//        String sql = "INSERT INTO `user` (`username`, `address`, `create_time`,\n" +
//                " `email`, `enable`, `password`,\n" +
//                " `phone`, `sex`, `update_time`, \n" +
//                " `hobby`, `birthday`, `remark`) VALUES (\n" +
//                " '" + userModel.getUsername() + "', '" + userModel.getAddress() + "', now(),\n" +
//                " '" + userModel.getEmail() + "', '" + userModel.getEnable() + "', '" + userModel.getPassword() + "', \n" +
//                " '" + userModel.getPhone() + "', '" + userModel.getSex() + "', sysdate(), \n" +
//                " '" + userModel.getHobby() + "', '" + userModel.getBirthday() + "', '');";
//        return JDBC.update(sql);
//        ？叫做占位符
        String sql = "INSERT INTO `user` (`username`, `address`, `create_time`,\n" +
                " `email`, `enable`, `password`,\n" +
                " `phone`, `sex`, `update_time`, \n" +
                " `hobby`, `birthday`, `remark`) VALUES (?,?,now()," +
                "?,?,?," +
                "?,?,now()," +
                "?,?,'')";
        List<Object> list = new ArrayList<>();
        list.add(userModel.getUsername());
        list.add(userModel.getAddress());
        list.add(userModel.getEmail());
        list.add(userModel.getEnable());
        list.add(userModel.getPassword());
        list.add(userModel.getPhone());
        list.add(userModel.getSex());
        list.add(userModel.getHobby());
        list.add(userModel.getBirthday());

        return JDBC.update(sql, list);
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "select address,id,username,phone,sex,hobby,date_format(birthday,'%Y-%m-%d') as birthday,enable,email  from user where id=" + userModel.getId();
        ResultSet resultSet = JDBC.query(sql);
        UserModel model = new UserModel();
        try {
            while (resultSet.next()) {
                model.setUsername(resultSet.getString("username"));
                model.setId(resultSet.getInt("id"));
                model.setPhone(resultSet.getString("phone"));
                model.setSex(resultSet.getString("sex"));
                model.setEnable(resultSet.getInt("enable"));
                model.setAddress(resultSet.getString("address"));
                model.setBirthday(resultSet.getString("birthday"));
                model.setHobby(resultSet.getString("hobby"));
                model.setEmail(resultSet.getString("email"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "UPDATE `user` SET \n" +
                "`username` = '" + userModel.getUsername() + "',\n" +
                " `address` = '" + userModel.getAddress() + "', \n" +
                " `email` = '" + userModel.getEmail() + "', \n" +
                " `enable` = '" + userModel.getEnable() + "',\n" +
                " `phone` = '" + userModel.getPhone() + "',\n" +
                " `sex` = '" + userModel.getSex() + "', \n" +
                " `update_time` =now(), \n" +
                " `hobby` = '" + (userModel.getHobby() == null ? "" : userModel.getHobby()) + "',\n" +
                " `birthday` = '" + userModel.getBirthday() + "',\n" +
                " `remark` = '' WHERE\n" +
                " (`id` = '" + userModel.getId() + "');";
        return JDBC.update(sql);
    }

    @Override
    public int delete(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId();
        return JDBC.update(sql);
    }
}
